package com.huaxia.dao;

import com.huaxia.pojo.SalesData;

import org.apache.ibatis.annotations.Select;

import java.util.List;


/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/04/22 10:27
 */

public interface SalesDataMapper {

    //每日预收
    @Select("select  SUM(PRESTADPREM) as dayAdvance from FACT_YX_PREPREM_LIST_day where RECEIVE_DATE=TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N'")
    Double getdayAdvance();
    //每日承保
    @Select("SELECT NVL(SUM(CASE WHEN ISSUE_DATE=TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD')  AND RNFLAG='N' THEN WRITTENSTADPREM  END ),0)-NVL(SUM(CASE WHEN YT_DATE=TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM/DD'),'YYYY/MM/DD')  AND RNFLAG='N' THEN WRITTENSTADPREM  END ),0)dayInSure from FACT_YX_PREPREM_LIST_day")
    Double getdayInSure();
    //每月预收
    @Select("select SUM(PRESTADPREM) as monthAdvance from FACT_YX_PREPREM_LIST_day where ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null)) or\n" +
            "(TO_CHAR(RECEIVE_DATE,'YYYY/MM') = TO_char(add_months(trunc(sysdate),-1),'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N')) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) AND RNFLAG='N'")
    Double getmonthAdvance();
    //每月承保
    @Select("SELECT SUM(CASE WHEN TO_CHAR(ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' THEN WRITTENSTADPREM  END )-SUM(CASE WHEN TO_CHAR(YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')  AND RNFLAG='N' THEN WRITTENSTADPREM  END )monthInSure from FACT_YX_PREPREM_LIST_day")
    Double getmonthInSure();
    //年承保
    @Select("SELECT SUM(CASE WHEN TO_CHAR(ISSUE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND RNFLAG='N' THEN WRITTENSTADPREM  END )-SUM(CASE WHEN TO_CHAR(YT_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')  AND RNFLAG='N' THEN WRITTENSTADPREM  END )yearInSure from FACT_YX_PREPREM_LIST_day")
    Double getyearInSure();
    //在册人力
    @Select("select count(*) from D_AGENT_PROVINCE_YX2 t where t.agentstate='在职' ")
    Integer getRegisteredHuman();
    //当月新人
    @Select("select count(*) from D_AGENT_PROVINCE_YX2 t where to_char(t.employdate,'yyyy/mm')=to_char(sysdate,'yyyy/mm') and t.agentstate='在职'")
    Integer getCurrentMonthHuman();
    //月度人力
    @Select("select SUM(case when NVL(cb.cbm,0)-NVL(yt.ytm,0)>='5000.00' then 1 end )\"actHuman\", SUM(case when NVL(cb.cbm,0)-NVL(yt.ytm,0)>='10000.00' then 1 end )\"goodHuman\" from\n" +
            "(select AGENTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and RNFLAG='N' and AGENTSTATE='在职' group by AGENTCODE)cb left join \n" +
            "(select AGENTCODE, SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and RNFLAG='N' and AGENTSTATE='在职' group by AGENTCODE)yt \n" +
            "on cb.AGENTCODE=yt.AGENTCODE")
    List<SalesData> getHuman();

    //营销年度目标
    @Select("select t.YX_TARGET from SD_SCREEN_TARGET t where t.id='1'")
    Long findYxTarget();

    //MDRT目标
    @Select("select t.MDRT_TARGET from SD_SCREEN_TARGET t where t.id='1'")
    Integer findMdrtTarget();

    //业绩表最新加载时间
    @Select("select max(t.loaddate) from FACT_YX_PREPREM_LIST_day t ")
    List<String> tableUpdateTime();
}
